package com.edu.hre.db.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.dao.BaseDao;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.recruit.CandidateForm;

public class BaseDaoHibernate extends HibernateDaoSupport implements BaseDao {

	public <T> T getPojo(final int pId, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<T>() {
			public T doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				c.add(Restrictions.eq("id", pId));
				return (T) c.uniqueResult();
			}
		});
	}

	public <T> List<T> getPojos(final String colName, final int compare, final Object colValue, final String[] orderBy, final Boolean[] desc,
			final int limit, final int offset, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				switch (compare) {
				case Compare.EQUAL:
					c.add(Restrictions.eq(colName, colValue));
					break;
				case Compare.NOT_EQUAL:
					c.add(Restrictions.ne(colName, colValue));
					break;
				case Compare.GREATER:
					c.add(Restrictions.gt(colName, colValue));
					break;
				case Compare.GREATER_EQUAL:
					c.add(Restrictions.ge(colName, colValue));
					break;
				case Compare.LESS:
					c.add(Restrictions.lt(colName, colValue));
					break;
				case Compare.LESS_EQUAL:
					c.add(Restrictions.le(colName, colValue));
					break;
				case Compare.IN:
					c.add(Restrictions.in(colName, (Object[]) colValue));
					break;
				case Compare.NOT_IN:
					c.add(Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
					break;
				case Compare.LIKE:
					c.add(Restrictions.like(colName, colValue).ignoreCase());
					break;
				case Compare.NOT_LIKE:
					c.add(Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
					break;
				case Compare.IS_EMPTY:
					c.add(Restrictions.isEmpty(colName));
					break;
				case Compare.IS_NOT_EMPTY:
					c.add(Restrictions.isNotEmpty(colName));
					break;
				case Compare.IS_NULL:
					c.add(Restrictions.isNull(colName));
					break;
				case Compare.IS_NOT_NULL:
					c.add(Restrictions.isNotNull(colName));
					break;
				default:
					break;
				}

				if (orderBy != null && desc != null && orderBy.length == desc.length) {
					for (int i = 0; i < desc.length; i++) {
						if (StringUtils.isNotEmpty(orderBy[i])) {
							if (desc[i]) {
								c.addOrder(Order.desc(orderBy[i]));
							} else {
								c.addOrder(Order.asc(orderBy[i]));
							}
						}
					}
				}
				if (limit > 0) {
					c.setMaxResults(limit);
				}
				if (offset > 0) {
					c.setFirstResult(offset);
				}
				return c.list();
			}
		});
	}

	public <T> List<T> getPojosOr(final String[] colNames, final int[] compares, final Object[] colValues, final String[] orderBy,
			final Boolean[] desc, final int limit, final int offset, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				Criterion cOr = null;
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];

						switch (compare) {
						case Compare.EQUAL:
							if (cOr == null) {
								cOr = Restrictions.eq(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.eq(colName, colValue));
							}
							break;
						case Compare.NOT_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ne(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ne(colName, colValue));
							}
							break;
						case Compare.GREATER:
							if (cOr == null) {
								cOr = Restrictions.gt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.gt(colName, colValue));
							}
							break;
						case Compare.GREATER_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ge(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ge(colName, colValue));
							}
							break;
						case Compare.LESS:
							if (cOr == null) {
								cOr = Restrictions.lt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.lt(colName, colValue));
							}
							break;
						case Compare.LESS_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.le(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.le(colName, colValue));
							}
							break;
						case Compare.IN:
							if (cOr == null) {
								cOr = Restrictions.in(colName, (Object[]) colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.in(colName, (Object[]) colValue));
							}
							break;
						case Compare.NOT_IN:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.in(colName, (Object[]) colValue));
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							}
							break;
						case Compare.LIKE:
							if (cOr == null) {
								cOr = Restrictions.like(colName, colValue).ignoreCase();
							} else {
								cOr = Restrictions.or(cOr, Restrictions.like(colName, colValue).ignoreCase());
							}
							break;
						case Compare.NOT_LIKE:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.like(colName, colValue).ignoreCase());
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							}
							break;
						case Compare.IS_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isEmpty(colName));
							}
							break;
						case Compare.IS_NOT_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isNotEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotEmpty(colName));
							}
							break;
						case Compare.IS_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNull(colName));
							}
							break;
						case Compare.IS_NOT_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNotNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotNull(colName));
							}
							break;
						default:
							break;
						}
					}
				c.add(cOr);
				if (orderBy != null && desc != null && orderBy.length == desc.length) {
					for (int i = 0; i < desc.length; i++) {
						if (StringUtils.isNotEmpty(orderBy[i])) {
							if (desc[i]) {
								c.addOrder(Order.desc(orderBy[i]));
							} else {
								c.addOrder(Order.asc(orderBy[i]));
							}
						}
					}
				}
				if (limit > 0) {
					c.setMaxResults(limit);
				}
				if (offset > 0) {
					c.setFirstResult(offset);
				}
				return c.list();
			}
		});
	}

	public <T> List<T> getPojosAnd(final String[] colNames, final int[] compares, final Object[] colValues, final String[] orderBy,
			final Boolean[] desc, final int limit, final int offset, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];
						switch (compare) {
						case Compare.EQUAL:
							c.add(Restrictions.eq(colName, colValue));
							break;
						case Compare.NOT_EQUAL:
							c.add(Restrictions.ne(colName, colValue));
							break;
						case Compare.GREATER:
							c.add(Restrictions.gt(colName, colValue));
							break;
						case Compare.GREATER_EQUAL:
							c.add(Restrictions.ge(colName, colValue));
							break;
						case Compare.LESS:
							c.add(Restrictions.lt(colName, colValue));
							break;
						case Compare.LESS_EQUAL:
							c.add(Restrictions.le(colName, colValue));
							break;
						case Compare.IN:
							c.add(Restrictions.in(colName, (Object[]) colValue));
							break;
						case Compare.NOT_IN:
							c.add(Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							break;
						case Compare.LIKE:
							c.add(Restrictions.like(colName, colValue).ignoreCase());
							break;
						case Compare.NOT_LIKE:
							c.add(Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							break;
						case Compare.IS_EMPTY:
							c.add(Restrictions.isEmpty(colName));
							break;
						case Compare.IS_NOT_EMPTY:
							c.add(Restrictions.isNotEmpty(colName));
							break;
						case Compare.IS_NULL:
							c.add(Restrictions.isNull(colName));
							break;
						case Compare.IS_NOT_NULL:
							c.add(Restrictions.isNotNull(colName));
							break;
						default:
							break;
						}
					}

				if (orderBy != null && desc != null && orderBy.length == desc.length) {
					for (int i = 0; i < desc.length; i++) {
						if (StringUtils.isNotEmpty(orderBy[i])) {
							if (desc[i]) {
								c.addOrder(Order.desc(orderBy[i]));
							} else {
								c.addOrder(Order.asc(orderBy[i]));
							}
						}
					}
				}

				if (limit > 0) {
					c.setMaxResults(limit);
				}
				if (offset > 0) {
					c.setFirstResult(offset);
				}
				return c.list();
			}
		});
	}

	public <T> boolean saveOrUpdate(Collection<T> pojos) throws Exception {
		try {
			getHibernateTemplate().saveOrUpdateAll(pojos);
			return true;
		} catch (Exception e) {
			throw e;
		}
	}

	public <T> boolean saveOrUpdate(T pojo) throws Exception {

		try {
			getHibernateTemplate().saveOrUpdate(pojo);
			return true;
		} catch (Exception e) {
			throw e;
		}
	}

	public <T> int sumByColumns(final String[] colNames, final int[] compares, final Object[] colValues, final String colName, final Class<T> _class)
			throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				c.setProjection(Projections.projectionList().add(Projections.sum(colName)));
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];

						switch (compare) {
						case Compare.EQUAL:
							c.add(Restrictions.eq(colName, colValue));
							break;
						case Compare.NOT_EQUAL:
							c.add(Restrictions.ne(colName, colValue));
							break;
						case Compare.GREATER:
							c.add(Restrictions.gt(colName, colValue));
							break;
						case Compare.GREATER_EQUAL:
							c.add(Restrictions.ge(colName, colValue));
							break;
						case Compare.LESS:
							c.add(Restrictions.lt(colName, colValue));
							break;
						case Compare.LESS_EQUAL:
							c.add(Restrictions.le(colName, colValue));
							break;
						case Compare.IN:
							c.add(Restrictions.in(colName, (Object[]) colValue));
							break;
						case Compare.NOT_IN:
							c.add(Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							break;
						case Compare.LIKE:
							c.add(Restrictions.like(colName, colValue).ignoreCase());
							break;
						case Compare.NOT_LIKE:
							c.add(Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							break;
						case Compare.IS_EMPTY:
							c.add(Restrictions.isEmpty(colName));
							break;
						case Compare.IS_NOT_EMPTY:
							c.add(Restrictions.isNotEmpty(colName));
							break;
						case Compare.IS_NULL:
							c.add(Restrictions.isNull(colName));
							break;
						case Compare.IS_NOT_NULL:
							c.add(Restrictions.isNotNull(colName));
							break;
						default:
							break;
						}
					}

				List<Integer> objs = (List<Integer>) c.list();
				if (objs != null && objs.size() > 0) {
					return objs.get(0);
				}
				return 0;
			}

		});
	}

	public <T> int countByColumns(final String[] colNames, final int[] compares, final Object[] colValues, final String colName, final Class<T> _class)
			throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				c.setProjection(Projections.projectionList().add(Projections.count(colName)));
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];

						switch (compare) {
						case Compare.EQUAL:
							c.add(Restrictions.eq(colName, colValue));
							break;
						case Compare.NOT_EQUAL:
							c.add(Restrictions.ne(colName, colValue));
							break;
						case Compare.GREATER:
							c.add(Restrictions.gt(colName, colValue));
							break;
						case Compare.GREATER_EQUAL:
							c.add(Restrictions.ge(colName, colValue));
							break;
						case Compare.LESS:
							c.add(Restrictions.lt(colName, colValue));
							break;
						case Compare.LESS_EQUAL:
							c.add(Restrictions.le(colName, colValue));
							break;
						case Compare.IN:
							c.add(Restrictions.in(colName, (Object[]) colValue));
							break;
						case Compare.NOT_IN:
							c.add(Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							break;
						case Compare.LIKE:
							c.add(Restrictions.like(colName, colValue).ignoreCase());
							break;
						case Compare.NOT_LIKE:
							c.add(Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							break;
						case Compare.IS_EMPTY:
							c.add(Restrictions.isEmpty(colName));
							break;
						case Compare.IS_NOT_EMPTY:
							c.add(Restrictions.isNotEmpty(colName));
							break;
						case Compare.IS_NULL:
							c.add(Restrictions.isNull(colName));
							break;
						case Compare.IS_NOT_NULL:
							c.add(Restrictions.isNotNull(colName));
							break;
						default:
							break;
						}
					}

				List<Integer> objs = (List<Integer>) c.list();
				if (objs != null && objs.size() > 0) {
					return objs.get(0);
				}
				return 0;
			}

		});
	}

	public <T> List<T> getPojosIn(final String colName, final Object[] colValues, final String[] orderBy, final Boolean[] desc, final int limit,
			final int offset, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
			public List<T> doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				if (StringUtils.isNotEmpty(colName) && colValues != null && colValues.length > 0) {
					c.add(Restrictions.in(colName, colValues));
				}
				if (orderBy != null && desc != null && orderBy.length == desc.length) {
					for (int i = 0; i < desc.length; i++) {
						if (StringUtils.isNotEmpty(orderBy[i])) {
							if (desc[i]) {
								c.addOrder(Order.desc(orderBy[i]));
							} else {
								c.addOrder(Order.asc(orderBy[i]));
							}
						}
					}
				}
				if (limit > 0) {
					c.setMaxResults(limit);
				}
				if (offset > 0) {
					c.setFirstResult(offset);
				}
				return c.list();
			}

		});
	}

	public <T> T getFirstPojoIn(String colName, Object[] colValues, String[] orderBy, Boolean[] desc, int offset, Class<T> _class) throws Exception {
		List<T> pojos = getPojosIn(colName, colValues, orderBy, desc, 1, offset, _class);
		if (pojos == null || pojos.size() == 0) {
			return null;
		}
		return (T) pojos.get(0);
	}

	public int update(String tbName, String[] colNames, Object[] colValues, String[] whereColNames, int[] whereCompares, Object[] whereColValues,
			String andOr) throws Exception {
		try {
			String upd = "update " + tbName + " set ";
			String where = "";
			if (whereColNames != null && whereColNames.length > 0) {
				if (!"and".equalsIgnoreCase(andOr) && !"or".equalsIgnoreCase(andOr)) {
					andOr = "and";
				}
				where = "where ";
				for (int i = 0; i < whereColNames.length; i++) {
					String colName = whereColNames[i];
					int compare = whereCompares[i];
					switch (compare) {
					case Compare.EQUAL:
						where += colName + " = ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.NOT_EQUAL:
						where += colName + " <> ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.GREATER:
						where += colName + " > ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.GREATER_EQUAL:
						where += colName + " >= ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.LESS:
						where += colName + " < ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.LESS_EQUAL:
						where += colName + " <= ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.IN:
						where += colName + " in ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.NOT_IN:
						where += colName + " not in ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.LIKE:
						where += colName + " like ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.NOT_LIKE:
						where += colName + " not like ?" + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					case Compare.IS_NOT_NULL:
						where += colName + " is not null " + (i < whereColNames.length - 1 ? (" " + andOr + " ") : "");
						break;
					default:
						break;
					}
				}
			}
			if (colNames != null && colNames.length > 0 && colValues != null && colValues.length == colNames.length) {
				for (int i = 0; i < colNames.length; i++) {
					upd += colNames[i] + " = ?" + (i < colNames.length - 1 ? ", " : "");
				}

				if (StringUtils.isNotBlank(where) && whereColValues != null && whereColValues.length > 0) {
					Object[] joinValues = new Object[colValues.length + whereColValues.length];
					System.arraycopy(colValues, 0, joinValues, 0, colValues.length);
					System.arraycopy(whereColValues, 0, joinValues, colValues.length, whereColValues.length);
					return getHibernateTemplate().bulkUpdate(upd + " " + where, joinValues);
				} else {
					return getHibernateTemplate().bulkUpdate(upd, colValues);
				}
			}
		} catch (Exception e) {
			throw e;
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> listArrayObject(final String sql, final int limit, final int offset,final Object...params) throws Exception {
		return (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				String sqlTmp = sql;
				sqlTmp = "select 1 z, tmpTable01380.* from (" + sqlTmp + ") tmpTable01380";
				Query c = s.createSQLQuery(sqlTmp);
				for(int i = 0; params != null && i < params.length; i++){
					c.setParameter(i, params[i]);
				}
				if (limit > 0) {
					c.setMaxResults(limit);
				}
				if (offset > 0) {
					c.setFirstResult(offset);
				}
				return c.list();
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public List<CandidateForm> listRecommendedCandidates(final String procedureName, final Object[] params) throws Exception {
		return (List<CandidateForm>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
		
				Connection con = null;
				Statement stmt = null;
				ResultSet rs = null;

				try {

					con = s.connection();
					stmt = con.createStatement();
					
					String sql = "CALL " + procedureName + "(";
					
					if (params.length <= 0) {
						sql += ");";
					} else if (params.length == 1) {
						sql += params[0].toString();
						sql += ");";
					} else {
						for (int ind = 0; ind < params.length - 1; ind++) {
							sql += params[ind].toString() + ", ";
						}
						sql += params[params.length-1].toString() + ");";
					}
					
					rs = stmt.executeQuery(sql);
					
					List<CandidateForm> allCans = new ArrayList<CandidateForm>();
	
					while (rs.next()) {
						
						try {
	
							CandidateForm can = new CandidateForm();
							can.setId(rs.getInt("col1") + "");
							can.setName(rs.getString("col2"));
							can.setEmail(rs.getString("col3"));
							can.setContactNumber(rs.getString("col4"));
							can.setScore(rs.getInt("col5"));
							
							allCans.add(can);
						} catch (Exception e) {
							CommonUtils.println(e.getMessage());
						}
					}

					return allCans;
				} catch (Exception e) {
					return new ArrayList<CandidateForm>();
				} finally {
					try {
						rs.close();
						stmt.close();
						con.close();
					} catch (Exception e) {
						CommonUtils.println(e.getMessage());
					}
				}
			}
		});
	}

	public <T> T getFirstPojo(String colName, int compare, Object colValue, String[] orderBy, Boolean[] desc, int offset, Class<T> _class)
			throws Exception {
		List<T> pojos = getPojos(colName, compare, colValue, orderBy, desc, 1, offset, _class);
		if (pojos == null || pojos.size() == 0) {
			return null;
		}
		return (T) pojos.get(0);
	}

	public <T> T getFirstPojoOr(String[] colNames, int[] compares, Object[] colValues, String[] orderBy, Boolean[] desc, int offset, Class<T> _class)
			throws Exception {
		List<T> pojos = getPojosOr(colNames, compares, colValues, orderBy, desc, 1, offset, _class);
		if (pojos == null || pojos.size() == 0) {
			return null;
		}
		return (T) pojos.get(0);
	}

	public <T> T getFirstPojoAnd(String[] colNames, int[] compares, Object[] colValues, String[] orderBy, Boolean[] desc, int offset, Class<T> _class)
			throws Exception {
		List<T> pojos = getPojosAnd(colNames, compares, colValues, orderBy, desc, 1, offset, _class);
		if (pojos == null || pojos.size() == 0) {
			return null;
		}
		return (T) pojos.get(0);
	}

	public <T> boolean deletePojo(T pojo) throws Exception {
		try {
			getHibernateTemplate().delete(pojo);
			return true;
		} catch (Exception e) {
			throw e;
		}
	}

	public <T> boolean deletePojos(Collection<T> pojos) throws Exception {
		try {
			getHibernateTemplate().deleteAll(pojos);
			return true;
		} catch (Exception e) {
			throw e;
		} 
	}

	public <T> int deletePojos(String sqlCommand, Object... params) throws Exception {
		return getHibernateTemplate().bulkUpdate(sqlCommand, params);
	}

	public <T> boolean deleteFirstPojo(String colName, int compare, Object colValue, String[] orderBy, Boolean[] desc, int offset, Class<T> _class) throws Exception {
		T obj = getFirstPojo(colName, compare, colValue, orderBy, desc, offset, _class);
		return deletePojo(obj);
	}

	public <T> boolean deleteFirstPojo(int pId, Class<T> _class)
			throws Exception {
		T obj = getPojo(pId, _class);
		return deletePojo(obj);
	}

	public <T> boolean save(Collection<T> pojos) throws Exception {
		getHibernateTemplate().save(pojos);
		return true;
	}

	public <T> boolean save(T pojo) throws Exception {
		getHibernateTemplate().save(pojo);
		return true;
	}

	public <T> boolean update(Collection<T> pojos) throws Exception {
		getHibernateTemplate().update(pojos);
		return true;
	}

	public <T> boolean update(T pojo) throws Exception {
		getHibernateTemplate().update(pojo);
		return true;
	}

	public <T> int sumByColumns(String colNames, int compares, Object colValues, String colSumName, Class<T> _class) throws Exception {
		return sumByColumns(new String[]{colNames}, new int[]{compares}, new Object[]{colValues}, colSumName, _class);
	}

	public <T> int countByColumns(String colNames, int compares, Object colValues, String colCountName, Class<T> _class) throws Exception {
		return countByColumns(new String[]{colNames}, new int[]{compares}, new Object[]{colValues}, colCountName, _class);
	}

	public <T> List<T> getAllPojos(Class<T> _class) throws Exception {
		return getPojos(null, -1, null, null, null, 0, 0, _class);
	}

	public <T> List<T> getAllPojos(String[] orderBy, Boolean[] desc, Class<T> _class) throws Exception {
		return getPojos(null, -1, null, orderBy, desc, 0, 0, _class);
	}

	public <T> List<T> getPojos(String colName, int compare, Object colValue, Class<T> _class) throws Exception {
		return getPojos(colName, compare, colValue, null, null, 0, 0, _class);
	}

	public <T> T getFirstPojo(String colName, int compare, Object colValue, Class<T> _class) throws Exception {
		return getFirstPojo(colName, compare, colValue, null, null, 0, _class);
	}

	public <T> T getFirstPojoOr(String[] colNames, int[] compares, Object[] colValues, Class<T> _class) throws Exception {
		return getFirstPojoOr(colNames, compares, colValues, null, null, 0, _class);
	}

	public <T> T getFirstPojoAnd(String[] colNames, int[] compares, Object[] colValues, Class<T> _class) throws Exception {
		return getFirstPojoAnd(colNames, compares, colValues, null, null, 0, _class);
	}

	public <T> T getFirstPojoIn(String colName, Object[] colValues, Class<T> _class) throws Exception {
		return getFirstPojoIn(colName, colValues, null, null, 0, _class);
	}

	public <T> int countByColumnsOr(final String[] colNames, final int[] compares, final Object[] colValues, final String colName, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				c.setProjection(Projections.projectionList().add(Projections.count(colName)));
				Criterion cOr = null;
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];

						switch (compare) {
						case Compare.EQUAL:
							if (cOr == null) {
								cOr = Restrictions.eq(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.eq(colName, colValue));
							}
							break;
						case Compare.NOT_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ne(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ne(colName, colValue));
							}
							break;
						case Compare.GREATER:
							if (cOr == null) {
								cOr = Restrictions.gt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.gt(colName, colValue));
							}
							break;
						case Compare.GREATER_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ge(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ge(colName, colValue));
							}
							break;
						case Compare.LESS:
							if (cOr == null) {
								cOr = Restrictions.lt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.lt(colName, colValue));
							}
							break;
						case Compare.LESS_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.le(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.le(colName, colValue));
							}
							break;
						case Compare.IN:
							if (cOr == null) {
								cOr = Restrictions.in(colName, (Object[]) colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.in(colName, (Object[]) colValue));
							}
							break;
						case Compare.NOT_IN:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.in(colName, (Object[]) colValue));
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							}
							break;
						case Compare.LIKE:
							if (cOr == null) {
								cOr = Restrictions.like(colName, colValue).ignoreCase();
							} else {
								cOr = Restrictions.or(cOr, Restrictions.like(colName, colValue).ignoreCase());
							}
							break;
						case Compare.NOT_LIKE:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.like(colName, colValue).ignoreCase());
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							}
							break;
						case Compare.IS_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isEmpty(colName));
							}
							break;
						case Compare.IS_NOT_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isNotEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotEmpty(colName));
							}
							break;
						case Compare.IS_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNull(colName));
							}
							break;
						case Compare.IS_NOT_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNotNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotNull(colName));
							}
							break;
						default:
							break;
						}
					}
				c.add(cOr);
				List<Integer> objs = (List<Integer>) c.list();
				if (objs != null && objs.size() > 0) {
					return objs.get(0);
				}
				return 0;
			}
		});
	}

	public <T> int sumByColumnsOr(final String[] colNames, final int[] compares, final Object[] colValues, final String colSumName, final Class<T> _class) throws Exception {
		return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session sess) throws HibernateException, SQLException {
				Criteria c = sess.createCriteria(_class);
				c.setProjection(Projections.projectionList().add(Projections.sum(colSumName)));
				Criterion cOr = null;
				if (colNames != null && compares != null && colValues != null && colNames.length == compares.length
						&& compares.length == colValues.length)
					for (int i = 0; i < colNames.length; i++) {
						String colName = colNames[i];
						Object colValue = colValues[i];
						int compare = compares[i];

						switch (compare) {
						case Compare.EQUAL:
							if (cOr == null) {
								cOr = Restrictions.eq(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.eq(colName, colValue));
							}
							break;
						case Compare.NOT_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ne(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ne(colName, colValue));
							}
							break;
						case Compare.GREATER:
							if (cOr == null) {
								cOr = Restrictions.gt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.gt(colName, colValue));
							}
							break;
						case Compare.GREATER_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.ge(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.ge(colName, colValue));
							}
							break;
						case Compare.LESS:
							if (cOr == null) {
								cOr = Restrictions.lt(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.lt(colName, colValue));
							}
							break;
						case Compare.LESS_EQUAL:
							if (cOr == null) {
								cOr = Restrictions.le(colName, colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.le(colName, colValue));
							}
							break;
						case Compare.IN:
							if (cOr == null) {
								cOr = Restrictions.in(colName, (Object[]) colValue);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.in(colName, (Object[]) colValue));
							}
							break;
						case Compare.NOT_IN:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.in(colName, (Object[]) colValue));
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.in(colName, (Object[]) colValue)));
							}
							break;
						case Compare.LIKE:
							if (cOr == null) {
								cOr = Restrictions.like(colName, colValue).ignoreCase();
							} else {
								cOr = Restrictions.or(cOr, Restrictions.like(colName, colValue).ignoreCase());
							}
							break;
						case Compare.NOT_LIKE:
							if (cOr == null) {
								cOr = Restrictions.not(Restrictions.like(colName, colValue).ignoreCase());
							} else {
								cOr = Restrictions.or(cOr, Restrictions.not(Restrictions.like(colName, colValue).ignoreCase()));
							}
							break;
						case Compare.IS_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isEmpty(colName));
							}
							break;
						case Compare.IS_NOT_EMPTY:
							if (cOr == null) {
								cOr = Restrictions.isNotEmpty(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotEmpty(colName));
							}
							break;
						case Compare.IS_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNull(colName));
							}
							break;
						case Compare.IS_NOT_NULL:
							if (cOr == null) {
								cOr = Restrictions.isNotNull(colName);
							} else {
								cOr = Restrictions.or(cOr, Restrictions.isNotNull(colName));
							}
							break;
						default:
							break;
						}
					}
				c.add(cOr);
				List<Integer> objs = (List<Integer>) c.list();
				if (objs != null && objs.size() > 0) {
					return objs.get(0);
				}
				return 0;
			}
		});
	}

}
